package server;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

public class DataBase {
    // JDBC driver name and database URL
    private static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
    private static final String DB_URL = "jdbc:derby://127.0.0.1:1527/Chat;create=true";

    //  Database credentials
    private static final String USER = "app";
    private static final String PASS = "app";

    public static void addMessage(Message... messages) {
        String insertStatement = "INSERT INTO MESSAGES (ID_FROM, ID_TO, ROOM, TEXT, DATE_TIME) VALUES ( ?, ?, ?, ?, ?)";
        try (Connection dbConnection = getDBConnection();
             PreparedStatement prepStmt = dbConnection.prepareStatement(insertStatement);) {
            for (Message msg : messages) {
                prepStmt.setString(1, msg.getClientFrom().toString());
                prepStmt.setString(2, msg.getNameTo());
                prepStmt.setString(3, msg.getRoom());
                prepStmt.setString(4, msg.getText());
                prepStmt.setString(5, msg.getDateTime());
                prepStmt.executeQuery();
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void addUser(String name, String pass) {
        String insertStatement =  "INSERT INTO CHAT_USER (NAME, PASS) VALUES (?, ?)";
        try (Connection dbConnection = getDBConnection();
             PreparedStatement prepStmt = dbConnection.prepareStatement(insertStatement);) {
            prepStmt.setString(1, name);
            prepStmt.setString(2, pass);
            prepStmt.executeQuery();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static List<Message> getHistoryByName(String name) {
        List<Message> messages = new ArrayList<Message>();
        String selectStatement = "SELECT ID_FROM, ID_TO, ROOM, TEXT, DATE_TIME FROM MESSAGES WHERE ID_FROM=? OR ID_TO=?";
        try (Connection dbConnection = getDBConnection();
             PreparedStatement prepStmt = dbConnection.prepareStatement(selectStatement);) {
            prepStmt.setString(1, name);
            prepStmt.setString(2, name);
            ResultSet resultSet = prepStmt.executeQuery();
            while (resultSet.next()) {
                messages.add(new Message(
                        resultSet.getString("ID_FROM"),
                        resultSet.getString("ID_TO"),
                        resultSet.getString("ROOM"),
                        resultSet.getString("TEXT"),
                        resultSet.getString("DATE_TIME")));

            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return messages;
    }

    public static List<Message> getHistoryByRoom(String room) {
        List<Message> messages = new ArrayList<Message>();
        String selectStatement = "SELECT ID_FROM, ID_TO, ROOM, TEXT, DATE_TIME FROM MESSAGES WHERE ROOM =?";
        try (Connection dbConnection = getDBConnection();
             PreparedStatement prepStmt = dbConnection.prepareStatement(selectStatement);) {
            prepStmt.setString(1, room);
            ResultSet resultSet = prepStmt.executeQuery();
            while (resultSet.next()) {
                messages.add(new Message(
                        resultSet.getString("ID_FROM"),
                        resultSet.getString("ID_TO"),
                        resultSet.getString("ROOM"),
                        resultSet.getString("TEXT"),
                        resultSet.getString("DATE_TIME")));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return messages;
    }

    private static Connection getDBConnection() {
        Connection dbConnection = null;
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
        try {
            dbConnection = DriverManager.getConnection(DB_URL, USER, PASS);
            return dbConnection;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return dbConnection;
    }

    public static void createTables() throws SQLException {
        String createTableUsers = "CREATE TABLE \"CHAT_USER\"\n" +
                "(\n" +
                "\"ID\" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n" +
                "\"NAME\" VARCHAR(150),\n" +
                "\"PASS\" VARCHAR(150)\n" +
                ")";
        String createTableMessages = "CREATE TABLE \"MESSAGES\"\n" +
                "(\n" +
                "\"ID\" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n" +
                "\"ID_FROM\" VARCHAR(150) NOT NULL,\n" +
                "\"ID_TO\" VARCHAR(150),\n" +
                "\"ROOM\" VARCHAR(150) NOT NULL,\n" +
                "\"TEXT\" VARCHAR(150) NOT NULL,\n" +
                "\"DATE_TIME\" VARCHAR(150)\n" +
                ")";
        try (Connection dbConnection = getDBConnection();
             Statement statement = dbConnection.createStatement();) {
            statement.execute(createTableMessages);
            statement.execute(createTableUsers);
        } catch (SQLException e) {
        }
    }

    public static Map<String, String> getUsers() {
        Map<String, String> users = new ConcurrentHashMap<>();
        try (Connection dbConnection = getDBConnection();
             Statement statement = dbConnection.createStatement();) {
            ResultSet rs = statement.executeQuery("SELECT NAME, PASS from CHAT_USER");
            while (rs.next()) {
                users.put(rs.getString("NAME"), rs.getString("PASS"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return users;
    }
}
